<?php
/**
 * 从推广平台获取订单数据
 * 2018-3-29
 * yumao
 */
namespace app\controllers\crontab;

use Ares333\Curl\Toolkit;
use yii;
use app\models\marketing\MailGroup;
use app\components\Site;
use app\models\marketing\OrderInfo;
use app\components\TemplateMarket;

use app\models\Elog;
use GuzzleHttp\Client;

class GetOrderInfoController extends \app\base\Controller
{
    /** @var int 从 GB 获取订单商品数据时默认每页的数量 */
    protected $pageSize = 100;

    // 获取gb下单接口头配置信息
    private $getGbOrderHeader = [
        'dev'   => [
            'method'   => 'innerOrderInfoList',
            'service'  => 'com.globalegrow.morder.spi.inter.OrderInfoService',
            'type'     => 1,
            'version'  => '1.0.0',
            'domain'   => '',
            'tokenId'  => '809fdb0bfa0b2cfb9f4474690d3a3cb4'
        ],
        'test'   => [
            'method'   => 'innerOrderInfoList',
            'service'  => 'com.globalegrow.morder.spi.inter.OrderInfoService',
            'type'     => 1,
            'version'  => '1.0.0',
            'domain'   => '',
            'tokenId'  => '809fdb0bfa0b2cfb9f4474690d3a3cb4'
        ],
        'product'   => [
            'method'   => 'innerOrderInfoList',
            'service'  => 'com.globalegrow.morder.spi.inter.OrderInfoService',
            'type'     => 1,
            'version'  => '1.0.0',
            'domain'   => '',
            'tokenId'  => '5dd73f1f0412b1a8f483ed0655b259b4'
        ]
    ];
    
    // 获取gb商品分类信息
    private $getGbGoodsCatIdHeader = [
        'dev'   => [
            'method'   => 'queryDefCategoryInfo',
            'service'  => 'com.globalegrow.mgoods.spi.inter.IGoodsCategoryRelationService',
            'type'     => 1,
            'version'  => '1.0.0',
            'domain'   => '',
            'tokenId'  => '809fdb0bfa0b2cfb9f4474690d3a3cb4'
        ],
        'test'   => [
            'method'   => 'queryDefCategoryInfo',
            'service'  => 'com.globalegrow.mgoods.spi.inter.IGoodsCategoryRelationService',
            'type'     => 1,
            'version'  => '1.0.0',
            'domain'   => '',
            'tokenId'  => '809fdb0bfa0b2cfb9f4474690d3a3cb4'
        ],
        'product'   => [
            'method'  => 'queryDefCategoryInfo',
            'service'  => 'com.globalegrow.mgoods.spi.inter.IGoodsCategoryRelationService',
            'type'     => 1,
            'version'  => '1.0.0',
            'domain'   => '',
            'tokenId'  => '5dd73f1f0412b1a8f483ed0655b259b4'
        ]
    ];
    
    // 获取gb下单接口地址
    private $gbOrderApiUrl = [
        'dev'   => 'http://10.40.2.62:2087/gateway/',
        'test'   => 'http://10.40.2.62:2087/gateway/',
        'product'   => 'http://gb-obs-outer.gw-soa.com/gateway/'
    ];

    /**
     * 获取服装站分类信息
     * yumao
     * 2018-6-14
     */
    public function actionGetCategoryData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        $selectSql = " select data_from,cat_id,cat_name from tb_eload_category";
        $categoryArr = yii::$app->ams_db->createCommand($selectSql)->queryAll();
        $i = 0;
        $insertArr = [];
        $insertPre = "insert into cloth_category_data(data_from,cat_id,cat_name) values ";
        $insertSql = "";
        foreach ($categoryArr as $categoryArrVal) {
            $insertArr[] = "({$categoryArrVal['data_from']},{$categoryArrVal['cat_id']},'".addslashes($categoryArrVal['cat_name'])."')";
            $i++;
            if ($i>=1000) { // 每次插入1000条数据
               $insertSql = $insertPre.join(",",$insertArr) ." ON DUPLICATE KEY UPDATE cat_name=values(cat_name)";
               Yii::$app->db->createCommand($insertSql)->execute();
               $i=0;
               $insertArr = [];
            }
        }
        
        if ($insertArr) { // 把剩余数据入库
            $insertSql = $insertPre.join(",",$insertArr)." ON DUPLICATE KEY UPDATE cat_name=values(cat_name)";
            Yii::$app->db->createCommand($insertSql)->execute();
        }
    }
    /**
     * 获取前一天的通过邮件渠道下单的相关数据
     * yumao
     * 2018-3-30
     */
    public function actionGetData()
    {
        set_time_limit(0);
        // $orderData = yii::$app->ams_db->createCommand($sql)->queryAll();
        $mothod = strtolower(Yii::$app->request->getMethod());
        $formData = Yii::$app->request->$mothod();
        $startTime = Yii::$app->request->$mothod('starttime');
        $endTime = Yii::$app->request->$mothod('endtime');
        $siteId = Yii::$app->request->$mothod('site_id');
        
        if (empty($startTime)) {
            $startTime = 0; 
        }
        if (empty($endTime)) {
            $endTime = 0; 
        }
        
        $orderInfo = new OrderInfo();
        $mailGroup = new MailGroup();
        
        $siteToWid = $orderInfo->getSiteToWid();
        $terminalList = $orderInfo->getTerminalList();
        
        if($siteId) {
            $siteToWid = [
                $siteId => $siteToWid[$siteId]
            ];
        }
        foreach($siteToWid as $siteId => $wid) {
                
                $orderData = $orderInfo->getData($startTime,$endTime,$wid);
                if ($orderData) {
                    
                    $orderInfoArr = $value4Arr = $orderMailInfoArr = [];
                    
                    foreach ($orderData as $orderDataVal) {
                        $orderInfoArrTmp = [];
                        $orderInfoArrTmp['data_from'] = addslashes($orderDataVal['data_from']);
                        $orderInfoArrTmp['site_id'] = $siteId;
                        $orderInfoArrTmp['order_id'] = addslashes($orderDataVal['order_id']);
                        $orderInfoArrTmp['order_sn'] = addslashes($orderDataVal['order_sn']);
                        $orderInfoArrTmp['user_id'] = addslashes($orderDataVal['user_id']);
                        $orderInfoArrTmp['add_time'] = addslashes($orderDataVal['add_time']);
                        $orderInfoArrTmp['add_date'] = \yii::$app->bjTime->date('Y-m-d', $orderInfoArrTmp['add_time']);
                        $orderInfoArrTmp['order_status'] = addslashes($orderDataVal['order_status']);
                        $orderInfoArrTmp['pay_id'] = addslashes($orderDataVal['pay_id']);
                        $orderInfoArrTmp['pay_time'] = addslashes($orderDataVal['pay_time']);
                        $orderInfoArrTmp['shipping_fee'] = addslashes($orderDataVal['shipping_fee']);
                        $orderInfoArrTmp['order_amount'] = addslashes($orderDataVal['order_amount']);
                        $orderInfoArrTmp['consignee'] = addslashes($orderDataVal['consignee']);
                        $orderInfoArrTmp['country'] = addslashes($orderDataVal['country']);
                        $orderInfoArrTmp['lang'] = addslashes($orderDataVal['lang']);
                        $orderInfoArr[] = $orderInfoArrTmp;
                        $orderMailInfoArrTmp = [];
                        $orderMailInfoArrTmp['wid'] = addslashes($orderDataVal['wid']);
                        $orderMailInfoArrTmp['site_id'] = $siteId;
                        $orderMailInfoArrTmp['order_sn'] = addslashes($orderDataVal['order_sn']);
                        $orderMailInfoArrTmp['is_last'] = addslashes($orderDataVal['is_last']);
                        $orderMailInfoArrTmp['value1'] = addslashes($orderDataVal['value1']);
                        $orderMailInfoArrTmp['value2'] = addslashes($orderDataVal['value2']);
                        $orderMailInfoArrTmp['value3'] = addslashes($orderDataVal['value3']);
                        $matchVal = $matchVal1 = [];
                        preg_match('/.*?\d{10}/is', $orderMailInfoArrTmp['value3'],$matchVal);
                        if (!empty($matchVal) && !empty($matchVal[0])) {
                            $value4Arr[] = $orderMailInfoArrTmp['value4'] = $matchVal[0];
                        } else {
                            $value4Arr[] = $orderMailInfoArrTmp['value4'] = $orderMailInfoArrTmp['value3'];
                        }

                        // 配置设备类型
                        preg_match('/^[A-Za-z]*/is', $orderMailInfoArrTmp['order_sn'],$matchVal1);
                        if (!empty($matchVal1) && !empty($matchVal1[0])) { // 说明有匹配到前缀
                            
                            if(isset($terminalList[$wid][$matchVal1[0]]) && $terminalList[$wid][$matchVal1[0]] == 'wap') {
                                $orderMailInfoArrTmp['terminal'] = 1;
                            } else if(isset($terminalList[$wid][$matchVal1[0]]) && $terminalList[$wid][$matchVal1[0]] == 'IOS') {
                                $orderMailInfoArrTmp['terminal'] = 2;
                            } else if(isset($terminalList[$wid][$matchVal1[0]]) && $terminalList[$wid][$matchVal1[0]] == 'andriod') {
                                $orderMailInfoArrTmp['terminal'] = 3;
                            } else {
                                $orderMailInfoArrTmp['terminal'] = 0;
                            }
                            

                        } else {
                            $orderMailInfoArrTmp['terminal'] = 0;
                        }

                        $orderMailInfoArr[] = $orderMailInfoArrTmp;
                    }
                    $groupInfoArr = $mailGroup->getGroupIdByUtmsource($value4Arr, $siteId);
                    $groupInfoArrDeal = $abTestGroupArr = $abTestTemIdToId = [];
                    foreach ($groupInfoArr as $groupInfoArrVal) {
                        $matchVal = [];
                        preg_match('/.*?\d{10}/is', $groupInfoArrVal['group_name'],$matchVal);
                        if (!empty($matchVal) && !empty($matchVal[0])) {
                            $groupInfoArrDeal[$matchVal[0]] = $groupInfoArrVal;
                            if($groupInfoArrVal['is_ab_test'] == 1) {
                                $abTestGroupArr[] = $groupInfoArrVal['template_id'];
                            }
                        }
                    }
                    
                    if($abTestGroupArr) {
                        $TemplateMarket = new TemplateMarket();
                        $templateInfo = $TemplateMarket->getTemplateIdByIds($abTestGroupArr);
                        foreach($templateInfo as $template) {
                            $abTestTemIdToId[$template['id']] = $template['template_id']; 
                        }
                    }
                    
                    foreach ($orderMailInfoArr as $orderMailInfoArrKey => $orderMailInfoArrVal) {
                        if (!empty($groupInfoArrDeal[$orderMailInfoArrVal['value4']])) {
                            $orderMailInfoArr[$orderMailInfoArrKey]['group_id'] = $groupInfoArrDeal[$orderMailInfoArrVal['value4']]['group_id'];
                            if($groupInfoArrDeal[$orderMailInfoArrVal['value4']]['is_ab_test'] == 1) {
                                $orderMailInfoArr[$orderMailInfoArrKey]['template_id'] = isset($abTestTemIdToId[$groupInfoArrDeal[$orderMailInfoArrVal['value4']]['template_id']]) ? $abTestTemIdToId[$groupInfoArrDeal[$orderMailInfoArrVal['value4']]['template_id']] : 0;
                            } else {
                                $orderMailInfoArr[$orderMailInfoArrKey]['template_id'] = $groupInfoArrDeal[$orderMailInfoArrVal['value4']]['template_id'];
                            }
                        } else {
                            $orderMailInfoArr[$orderMailInfoArrKey]['group_id'] = 0;
                            $orderMailInfoArr[$orderMailInfoArrKey]['template_id'] = 0;
                        }
                    }
                    $orderInfo->addOrderMailInfo($orderMailInfoArr);
                    $orderInfo->addOrderInfo($orderInfoArr);
                    echo Yii::$app->params['sites']['marketing_email']['web_index'][$siteId].'数据入库完毕';
                    Elog::logCrontab(Yii::$app->params['sites']['marketing_email']['web_index'][$siteId].'数据入库完毕', 'actionGetData','info');
                } else {
                    echo Yii::$app->params['sites']['marketing_email']['web_index'][$siteId].'没有查询到订单数据';
                    Elog::logCrontab(Yii::$app->params['sites']['marketing_email']['web_index'][$siteId].'没有查询到订单数据', 'actionGetData','info');
                }
        
        }
    }
   
    /*
     * 手动解析zaful下单数据的设备类型
     */
    public function actionDealZafulOrderTerminal()
    {
         set_time_limit(0);
         
         $getSql = 'select id,order_sn from vw_order_mail_info where terminal = 0'; 
         $orderDatas = Yii::$app->db->createCommand($getSql)->queryAll();
         
         $update = [];
         foreach($orderDatas as $orderData) {
             $matchVal1 = [];
             // 配置设备类型
            preg_match('/^[A-Za-z]*/is', $orderData['order_sn'],$matchVal1);
            if (!empty($matchVal1) && !empty($matchVal1[0])) { // 说明有匹配到前缀
                if($matchVal1[0] == 'UM' || $matchVal1[0] == 'UL') {
                    
                    $update[] = 'WHEN '.$orderData['id'].' THEN 1 ';
                } else if($matchVal1[0] == 'UA') {
                    
                    $update[] = 'WHEN '.$orderData['id'].' THEN 2 ';
                } else if($matchVal1[0] == 'UB') {
                    
                    $update[] = 'WHEN '.$orderData['id'].' THEN 3 ';
                }

            }
         }
         
         // 更新数据
         if($update) {
             $sql = 'update vw_order_mail_info set terminal = CASE id '.join('',$update).' END';
             Yii::$app->db->createCommand($sql)->execute();
         }
         
         $getSql = 'select id,add_time from tb_eload_order_info where add_date = 0'; 
         $orderDatas = Yii::$app->db->createCommand($getSql)->queryAll();
         
         $update = [];
         foreach($orderDatas as $orderData) {
             $update[] = 'WHEN '.$orderData['id'].' THEN "'.\yii::$app->bjTime->date('Y-m-d', $orderData['add_time']).'" ';
         }
         // 更新数据
         if($update) {
             $sql = 'update tb_eload_order_info set add_date = CASE id '.join('',$update).' END';
             Yii::$app->db->createCommand($sql)->execute();
         }
         
         return '数据处理完成！';
    }
    /**
     * 抓取订单具体商品信息数据
     * yumao 
     * 2018-3-30
     */
    public function actionGetGoodData()
    {
        set_time_limit(0);
        $mothod = strtolower(Yii::$app->request->getMethod());
        $formData = Yii::$app->request->$mothod();
        $startTime = Yii::$app->request->$mothod('starttime');
        $endTime = Yii::$app->request->$mothod('endtime');
        $siteId = Yii::$app->request->$mothod('site_id');
        if (empty($startTime)) {
            $startTime = 0; 
        }
        if (empty($endTime)) {
            $endTime = 0; 
        }
        
        $orderInfo = new OrderInfo();
        $siteToWid = $orderInfo->getSiteToWid();
        
        if($siteId) {
            $siteToWid = [
                $siteId => $siteToWid[$siteId]
            ];
        }
        
        foreach($siteToWid as $siteId => $wid) {
        
            $orderInfo->getGoodsInfoByOrder($startTime,$endTime,$siteId,$wid);
        }
        echo '数据处理完毕';
    }
    
    /**
     * 定时获取并保存gb站点通过edm渠道发送的订单和订单商品数据
     * /crontab/get-order-info/get-gb-order-goods-data?site=marketing_email 每天执行一次
     * @user liugang
     * @date 2018-4-27
     * @deprecated 已废弃
     */
    public function actionGetGbOrderGoodsData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $gets = Yii::$app->request->get();
        $limit = isset($gets['limit']) ? intval($gets['limit']) : 5000;
        
        $endTime = \yii::$app->bjTime->strtotime(\yii::$app->bjTime->date("Y-m-d")." 00:00:00");
        $startTime = isset($gets['start_date']) ? \yii::$app->bjTime->strtotime($gets['start_date']." 00:00:00") : $endTime-24*3600;
        
        for($j=0; $j<2; $j++) {
        
            // 变量初始化
            $sqlArr = $getOrderGoodsSqlArr = [];
            // 由于gb站订单分表128张，分两次union all查询，循环拼接sql
            // 
            
            for($i=$j*64; $i<($j+1)*64; $i++) {
                $sqlArr[] = ' select '.
                        'o.site_code,o.order_sn,o.user_id,o.pipeline_code,o.order_language,o.created_time,o.pay_status,o.pay_type,o.pay_channel,o.completed_time,o.order_amount,o.shipping_amount,'.
                        'e.platform,e.utm_medium,e.utm_source,e.utm_campaign,a.email,a.country,a.country_code,a.province,a.city '.
                        'from order_info_'.$i.' o join order_info_extend_'.$i.' e on o.order_sn = e.order_sn left join order_address_'.$i.' a on o.order_sn = a.order_sn where o.process_version = 0 and e.utm_medium in("email","mail") and o.completed_time >= '.$startTime.' and o.completed_time < '.$endTime.' '; 

                $getOrderGoodsSqlArr[] = ' select order_sn,goods_sn,goods_type,goods_label_id,price_type,shop_code,operation_type,qty,goods_amount from order_goods_'.$i;
            }

            // 拼接成最终的执行sql
            if($sqlArr) {
            
                $start = 0;
                while(1) { // 循环分批次去去数据

                    $querySql = join('union all', $sqlArr).' order by created_time asc limit '.$start.','.$limit;

                    $datas = Yii::$app->gb_order_db->createCommand($querySql)->queryAll();
                    if(!$datas) {
                        break;
                    }

                    $orderSnArr = $orderInsertArr = $orderMarketInsertArr = [];
                    $insertSql = "insert into gearbest_order_sys_data(site_code,user_id,pipeline_code,order_sn,platform,utm_medium,utm_source,utm_campaign,order_language,created_time,pay_status,pay_type,pay_channel,completed_date,completed_time,order_amount,shipping_amount,email,country,country_code,province,city) values";
                    $insertMarketSql = "insert into gearbest_order_market_data(site_code,user_id,pipeline_code,order_sn,platform,utm_medium,utm_source,utm_campaign,order_language,created_time,pay_status,pay_type,pay_channel,completed_date,completed_time,order_amount,shipping_amount,email,country,country_code,province,city) values";
                    foreach($datas as $data) {
                        
                        if($data['utm_source'] == 'mail_sys' || $data['utm_source'] == 'email_sys' || ($data['utm_source'] == 'others' && !preg_match("/^GB_[A-Za-z0-9_]+_\d+_\d+.*?/i", $data['utm_campaign']))) { // 系统邮件下单数据
                            $orderInsertArr[] = "('{$data['site_code']}',{$data['user_id']},'{$data['pipeline_code']}','{$data['order_sn']}','{$data['platform']}','{$data['utm_medium']}','{$data['utm_source']}','{$data['utm_campaign']}','{$data['order_language']}','{$data['created_time']}','{$data['pay_status']}','{$data['pay_type']}','{$data['pay_channel']}','".\yii::$app->bjTime->date("Y-m-d",$data['completed_time'])."','{$data['completed_time']}','{$data['order_amount']}','{$data['shipping_amount']}','{$data['email']}','{$data['country']}','{$data['country_code']}','{$data['province']}','{$data['city']}')";
                            $orderSnArr[] = '"'.$data['order_sn'].'"';
                        } else if($data['utm_source'] == 'mail_api' || ($data['utm_source'] == 'others' && preg_match("/^GB_[A-Za-z0-9_]+_\d+_\d+.*?/i", $data['utm_campaign']))) { // 营销邮件下单数据
                            $orderMarketInsertArr[] = "('{$data['site_code']}',{$data['user_id']},'{$data['pipeline_code']}','{$data['order_sn']}','{$data['platform']}','{$data['utm_medium']}','{$data['utm_source']}','{$data['utm_campaign']}','{$data['order_language']}','{$data['created_time']}','{$data['pay_status']}','{$data['pay_type']}','{$data['pay_channel']}','".\yii::$app->bjTime->date("Y-m-d",$data['completed_time'])."','{$data['completed_time']}','{$data['order_amount']}','{$data['shipping_amount']}','{$data['email']}','{$data['country']}','{$data['country_code']}','{$data['province']}','{$data['city']}')";
                            $orderSnArr[] = '"'.$data['order_sn'].'"';
                        }
                       
                    }

                    if($orderInsertArr) {
                        $insertSql .= join(',',$orderInsertArr);
                        // 数据写入edm数据表
                        Yii::$app->db->createCommand($insertSql)->execute();
                    }
                    
                    if($orderMarketInsertArr) {
                        $insertMarketSql .= join(',',$orderMarketInsertArr);
                        // 数据写入edm数据表
                        Yii::$app->db->createCommand($insertMarketSql)->execute();
                    }

                    // 获取相应订单的订单商品数据
                    if($orderSnArr) {
                        
                        foreach($getOrderGoodsSqlArr as $key => $val) {
                            $getOrderGoodsSqlArr[$key] = $val.' where order_sn in ('.join(',',$orderSnArr).') ';
                        }
                        
                        $goodsStart = 0;
                        while(1) {
                            $queryOrderGoodsSql = join('union all', $getOrderGoodsSqlArr).' limit '.$goodsStart.','.$limit;

                            $goodsDatas = Yii::$app->gb_order_db->createCommand($queryOrderGoodsSql)->queryAll();

                            if(!$goodsDatas) {
                                break;
                            }

                            $goodSnArr = $orderGoodsInsertArr = $goodsSnCatIdArr = [];

                            foreach($goodsDatas as $goodsData) {
                                $goodSnArr[] = '"'.$goodsData['goods_sn'].'"';
                            }

                            // 获取商品对应的最小分类id
                            if($goodSnArr) {
                                $getGoodsCatIdSql = "select category_id,good_sn from goods_category_relation where site_code = 'GB' and is_default = 1 and good_sn in(".join(',',$goodSnArr).")";
                                $goodsCatIds = Yii::$app->gb_goods_db->createCommand($getGoodsCatIdSql)->queryAll();
                                foreach($goodsCatIds as $goodsCatId) {
                                    $goodsSnCatIdArr[$goodsCatId['good_sn']] = $goodsCatId['category_id'];
                                }

                            }

                            $insertGoodsSql = "insert into gearbest_order_goods_data(goods_sn,order_sn,goods_type,goods_label_id,price_type,shop_code,operation_type,qty,goods_amount,category_id) values";
                            foreach($goodsDatas as $goodsData) {
                                $goodsCategoryId = isset($goodsSnCatIdArr[$goodsData['goods_sn']]) ? $goodsSnCatIdArr[$goodsData['goods_sn']] : 0;
                                $orderGoodsInsertArr[] = "('{$goodsData['goods_sn']}','{$goodsData['order_sn']}','{$goodsData['goods_type']}','{$goodsData['goods_label_id']}','{$goodsData['price_type']}','{$goodsData['shop_code']}','{$goodsData['operation_type']}','{$goodsData['qty']}','{$goodsData['goods_amount']}','{$goodsCategoryId}')";
                            }

                            if($orderGoodsInsertArr) {
                                $insertGoodsSql .= join(',',$orderGoodsInsertArr);
                                // 数据写入edm数据表
                                Yii::$app->db->createCommand($insertGoodsSql)->execute();
                            }


                            $goodsStart += $limit;
                            echo '已处理第'.($j+1).'批订单商品数据'.$goodsStart.'<br/>';
                            Elog::logCrontab('已处理第'.($j+1).'批订单商品数据'.$goodsStart, 'actionGetGbOrderGoodsData','info');
                        }
                    }

                    $start += $limit; 
                    
                    // 休眠1s
                    sleep(1);
                    echo '已处理第'.($j+1).'批数据'.$start.'<br/>';
                    Elog::logCrontab('已处理第'.($j+1).'批数据'.$start, 'actionGetGbOrderGoodsData','info');
                }

            }
            // 休眠2s
            sleep(2);
            echo '处理第'.($j+1).'批数据完成<br/>';
            Elog::logCrontab('处理第'.($j+1).'批数据完成', 'actionGetGbOrderGoodsData','info');
        }
        Elog::logCrontab("GB订单数据同步完成！", 'actionGetGbOrderGoodsData','info');
        return "GB订单数据同步完成！";
    }
    /**
     * yumao
     * 2018-6-13
     * gb站根据sku获取分类id值
     */
    public function actionGetGbOrderGoodsCatId()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        yii::$app->db->enableSlaves = false; 
        $client = new Client(['timeout' => 60]);
        
       
        // 查询没有获取到category_id的商品信息
        $selectCount = " select count(*) from (select count(*) as notCategoryNums from gearbest_order_goods_data where category_id = 0 group by goods_sn) as gogd";
        $notCategoryCount = Yii::$app->db->createCommand($selectCount)->queryScalar();
        $times = ceil($notCategoryCount/1000);
        for ($i=0;$i<=$times;$i++) {
            $selectSql = " select  goods_sn from gearbest_order_goods_data where category_id = 0 group by goods_sn order by id desc limit 1000 "; 
            $goodsSnArr = Yii::$app->db->createCommand($selectSql)->queryAll();
            $goodsSnList = [];
            foreach ($goodsSnArr as $goodsSnArrVal) {
                $goodsSnList[] = $goodsSnArrVal['goods_sn'];
            }
            $postData = [];
            $postData['header'] = $this->getGbGoodsCatIdHeader[YII_ENV];
            $postData['body'] = [
                'siteCode'          => 'GB',             // 站点编码
                'pageNum'             => 1,       // 页码
                'pageSize'             => 1000,      // 每页大小
                "lang" =>  "en" ,
                "startNum" => 0 ,
                "goodSnList" => $goodsSnList,
            ]; 
            $response = $client->request('POST', $this->gbOrderApiUrl[YII_ENV], ['json' => $postData]);
            try {
                $responseJsonString = $response->getBody()->getContents();
                $returnData = \GuzzleHttp\json_decode($responseJsonString,true);
                if ($returnData['body']) {
                   $returnBody  = \GuzzleHttp\json_decode($returnData['body'],true);
                }
                if($returnBody['code'] == 0) {
                    $categoryInfo = $returnBody['data'];

                    // 分类id相同的合并掉，一次更新
                    $catIdSku = [];
                    foreach ($categoryInfo as $categoryInfoVal) {
                        $catIdSku[$categoryInfoVal['categoryId']][] = addslashes($categoryInfoVal['goodSn']);
                    }

                    foreach ($catIdSku as $catId => $catIdSkuVal) {
                        if ($catIdSkuVal) {
                            $skuStr = join("','",$catIdSkuVal);
                            $updateSql = " update gearbest_order_goods_data set category_id = {$catId} where goods_sn in ('{$skuStr}')";
                            Yii::$app->db->createCommand($updateSql)->execute();
                        }
                    }
                }
            } catch (\Exception $ex) {
                $msg = $ex->getMessage();
                echo $msg;
                Elog::logCrontab($msg,__FUNCTION__,'exception');
            }
        }
    }
    /**
     * 定时获取并保存gb站点通过edm渠道发送的订单和订单商品数据
     * /crontab/get-order-info/new-get-gb-order-goods-data?site=marketing_email 每天执行一次
     * @user liugang
     * @date 2018-4-27
     * @deprecated 2018-09-10 chenshanwen 废弃这个方法
     */
    public function actionNewGetGbOrderGoodsData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        
       
        $gets = Yii::$app->request->get();
        
        // 获取前12小时的数据
        $endTime = isset($gets['end_time']) && $gets['end_time'] ? intval($gets['end_time']) : \yii::$app->bjTime->strtotime(\yii::$app->bjTime->date("Y-m-d H").":00:00");
        $startTime = isset($gets['start_time']) && $gets['start_time'] ? intval($gets['start_time']) : $endTime-12*3600;
        
        
        $curPage = isset($gets['curPage']) ? $gets['curPage'] : 1; // 当前页
        $totalOrderNums = isset($gets['totalOrderNums']) ? $gets['totalOrderNums'] : 0; // 当前条件总数
        
        $pageSize = 100;
        
        $curTime = \yii::$app->bjTime->time();
        
        $client = new Client(['timeout' => 600]);
        
        $postData = [];
        $postData['header'] = $this->getGbOrderHeader[YII_ENV];
        $postData['body'] = [
            'siteCode'          => 'GB',             // 站点编码
            'createStartTime'   => $startTime,         // 订单创建开始时间
            'createEndTime'     => $endTime,         // 订单创建结束时间
//            'completedStartTime'  => 0,       // 订单支付完成开始时间
//            'completedEndTime'    => 0,       // 订单支付完成结束时间
            'pageNum'             => $curPage,       // 页码
            'pageSize'             => $pageSize,      // 每页大小
            'payStatusList'             => [0,1,2,3,4,5], // 支付状态列表 支付状态：0-未支付，1-部分支付，2-pending，3-已支付,4-退款中，5-已退款
            'orderStatusList'             => [0,1,2,3,4,5],// 订单状态列表 订单状态：0-正常,1-用户取消,2-系统取消,3-运营取消,4-Oms取消,5-取消申请中
            'utmMediumList'             => ["email","mail"]  // edm推广渠道列表
        ];
        $response = $client->request('POST', $this->gbOrderApiUrl[YII_ENV], ['json' => $postData]);
        
         try{ 
            $responseJsonString = $response->getBody()->getContents();
            $returnData = \GuzzleHttp\json_decode($responseJsonString,true);
            if ($returnData['body']) {
               $returnBody  = \GuzzleHttp\json_decode($returnData['body'],true);
            }
//            var_dump($returnBody);exit;
            if($returnBody['code'] == 0) {
                
                $totalOrderNums = $totalOrderNums ? $totalOrderNums : (isset($returnBody['data']['totalCount']) ? intval($returnBody['data']['totalCount']) : 0); // 当前条件下订单总数
                $pageNums = $totalOrderNums ? ceil($totalOrderNums/$pageSize) : 1;
                
                // 处理数据
                if($returnBody['data']['list']) {
                    
                    $orderInsertArr = $orderMarketInsertArr = $orderGoodsInsertArr = [];
                    $insertSql = "insert into gearbest_order_sys_data(site_code,user_id,pipeline_code,order_sn,platform,utm_medium,utm_source,utm_campaign,order_language,created_time,created_date,pay_status,pay_type,pay_channel,completed_date,completed_time,order_amount,shipping_amount,email,country,country_code,province,city,add_time) values";
                    $insertMarketSql = "insert into gearbest_order_market_data(site_code,user_id,pipeline_code,order_sn,platform,utm_medium,utm_source,utm_campaign,order_language,created_time,created_date,pay_status,pay_type,pay_channel,completed_date,completed_time,order_amount,shipping_amount,email,country,country_code,province,city,add_time,deal_utm_campaign) values";
                    

                    $insertGoodsSql = "insert into gearbest_order_goods_data(goods_sn,goods_name,order_sn,goods_type,goods_label_id,price_type,shop_code,operation_type,qty,goods_amount,add_time) values";

                            
                    foreach($returnBody['data']['list'] as $data) {
                        if(isset($data['innerOrderInfoExtendResp'])) {
                            if($data['innerOrderInfoExtendResp']['utmSource'] == 'mail_sys' || $data['innerOrderInfoExtendResp']['utmSource'] == 'email_sys' || ($data['innerOrderInfoExtendResp']['utmSource'] == 'others' && !preg_match("/.*?\d{10}/is", $data['innerOrderInfoExtendResp']['utmCampaign']))) { // 系统邮件下单数据
                                $orderInsertArr[] = "('{$data['siteCode']}','{$data['userId']}',\"".$data['pipelineCode']."\",'{$data['innerOrderInfoExtendResp']['orderSn']}','{$data['innerOrderInfoExtendResp']['platform']}','{$data['innerOrderInfoExtendResp']['utmMedium']}','{$data['innerOrderInfoExtendResp']['utmSource']}','{$data['innerOrderInfoExtendResp']['utmCampaign']}',\"".$data['orderLanguage']."\",'{$data['createdTime']}','".\yii::$app->bjTime->date("Y-m-d",$data['createdTime'])."','{$data['payStatus']}','{$data['payType']}','{$data['payChannel']}','".\yii::$app->bjTime->date("Y-m-d",$data['completedTime'])."','{$data['completedTime']}','{$data['orderAmount']}','{$data['shippingAmount']}','{$data['innerOrderAddressResp']['email']}',\"".$data['innerOrderAddressResp']['country']."\",'{$data['innerOrderAddressResp']['countryCode']}',\"".addslashes($data['innerOrderAddressResp']['province'])."\",\"".addslashes($data['innerOrderAddressResp']['city'])."\",".$curTime.")";
                               
                            } else if($data['innerOrderInfoExtendResp']['utmSource'] == 'mail_api' || ($data['innerOrderInfoExtendResp']['utmSource'] == 'others' && preg_match("/.*?\d{10}/is", $data['innerOrderInfoExtendResp']['utmCampaign']))) { // 营销邮件下单数据
                                
                                preg_match("/.*?\d{10}/is", $data['innerOrderInfoExtendResp']['utmCampaign'], $matches);
                                $dealUtmCompaign = !empty($matches) && !empty($matches[0]) ? $matches[0] : '';
                                $orderMarketInsertArr[] = "('{$data['siteCode']}','{$data['userId']}',\"".$data['pipelineCode']."\",'{$data['innerOrderInfoExtendResp']['orderSn']}','{$data['innerOrderInfoExtendResp']['platform']}','{$data['innerOrderInfoExtendResp']['utmMedium']}','{$data['innerOrderInfoExtendResp']['utmSource']}','{$data['innerOrderInfoExtendResp']['utmCampaign']}',\"".$data['orderLanguage']."\",'{$data['createdTime']}','".\yii::$app->bjTime->date("Y-m-d",$data['createdTime'])."','{$data['payStatus']}','{$data['payType']}','{$data['payChannel']}','".\yii::$app->bjTime->date("Y-m-d",$data['completedTime'])."','{$data['completedTime']}','{$data['orderAmount']}','{$data['shippingAmount']}','{$data['innerOrderAddressResp']['email']}',\"".$data['innerOrderAddressResp']['country']."\",'{$data['innerOrderAddressResp']['countryCode']}',\"".addslashes($data['innerOrderAddressResp']['province'])."\",\"".addslashes($data['innerOrderAddressResp']['city'])."\",".$curTime.",'".$dealUtmCompaign."')";
                               
                            }
                        }
                        if(isset($data['innerOrderGoodsRespList']) && is_array($data['innerOrderGoodsRespList'])) {
                            foreach($data['innerOrderGoodsRespList'] as $goodsData) {
                                $goodsData['goodsTitle'] = addslashes($goodsData['goodsTitle']);
                                $orderGoodsInsertArr[] = "('{$goodsData['goodsSn']}','{$goodsData['goodsTitle']}','{$goodsData['orderSn']}','{$goodsData['goodsType']}','{$goodsData['goodsLabelId']}','{$goodsData['priceType']}','{$goodsData['shopCode']}',\"".$goodsData['operationType']."\",'{$goodsData['qty']}','{$goodsData['goodsAmount']}',{$curTime})";

                            }
                        }
                        
                    }

                    if($orderInsertArr) {
                        $insertSql .= join(',',$orderInsertArr).' ON DUPLICATE KEY UPDATE add_time = '.$curTime.',platform=values(platform),utm_medium=values(utm_medium),utm_source=values(utm_source),utm_campaign=values(utm_campaign),pay_status=values(pay_status),pay_type=values(pay_type),pay_channel=values(pay_channel),completed_date=values(completed_date),completed_time=values(completed_time),order_amount=values(order_amount),shipping_amount=values(shipping_amount),created_time=values(created_time),created_date=values(created_date)';
                        // 数据写入edm数据表
                        Yii::$app->db->createCommand($insertSql)->execute();
                    }
                    
                    if($orderMarketInsertArr) {
                        $insertMarketSql .= join(',',$orderMarketInsertArr).' ON DUPLICATE KEY UPDATE add_time = '.$curTime.',platform=values(platform),utm_medium=values(utm_medium),utm_source=values(utm_source),utm_campaign=values(utm_campaign),pay_status=values(pay_status),pay_type=values(pay_type),pay_channel=values(pay_channel),completed_date=values(completed_date),completed_time=values(completed_time),order_amount=values(order_amount),shipping_amount=values(shipping_amount),deal_utm_campaign=values(deal_utm_campaign),created_time=values(created_time),created_date=values(created_date)';
                        // 数据写入edm数据表
                        Yii::$app->db->createCommand($insertMarketSql)->execute();
                    }
                    
                    if($orderGoodsInsertArr) {
                        $insertGoodsSql .= join(',',$orderGoodsInsertArr).' ON DUPLICATE KEY UPDATE add_time = '.$curTime.',goods_type=values(goods_type),goods_label_id=values(goods_label_id),price_type=values(price_type),shop_code=values(shop_code),operation_type=values(operation_type),qty=values(qty),goods_amount=values(goods_amount)';
                        // 数据写入edm数据表
                        Yii::$app->db->createCommand($insertGoodsSql)->execute();
                    }
                    
                    
                }
                
                
                if($curPage < $pageNums) {
                    $curPage++;
                    $host = YII_ENV_PROD ?  'http://ems.appinthestore.com/' : 'http://a.edmlocal.com/';
                    $redirectUrl = $host.'crontab/get-order-info/new-get-gb-order-goods-data?site=marketing_email&start_time='.$startTime.'&end_time='.$endTime.'&curPage='.$curPage.'&totalOrderNums='.$totalOrderNums;
                    
                    Elog::logCrontab($curPage.'-'.$totalOrderNums.'-'.$redirectUrl, 'actionNewGetGbOrderGoodsData','info');
                    
                    $client->request('GET', $redirectUrl);
                    exit;
                }
                
                Elog::logCrontab($curPage.'-'.$totalOrderNums.'数据同步完成！', 'actionNewGetGbOrderGoodsData','info');
                echo '数据同步完成！';
                
                
            } else {
                
                Elog::logCrontab($curPage.'-'.$totalOrderNums.'请求数据失败！', 'actionNewGetGbOrderGoodsData','info');
                echo '请求数据失败！';
            }
            
           
        } catch(\Exception $exp) {
            $msg = $exp->getCode().$exp->getLine().$exp->getMessage().$exp->getTraceAsString();
            echo $msg;
            Elog::logCrontab($msg, 'actionNewGetGbOrderGoodsData','info');
        }
        
        
    }

    /**
     * 从 GB 获取订单数据，这个方法只获取总数，然后多线程调用 actionGetGbOrderGoodsDataByPage 这个方法分页获取数据
     * @throws \GuzzleHttp\Exception\GuzzleException
     */
    public function actionGetGbOrderGoodsDataV2()
    {
        $gets = Yii::$app->request->get();
        // 没有指定时间获取则获取前12小时的数据
        $endTime = isset($gets['end_time']) && $gets['end_time'] ? intval($gets['end_time']) : \yii::$app->bjTime->strtotime(\yii::$app->bjTime->date("Y-m-d H").":00:00");
        $startTime = isset($gets['start_time']) && $gets['start_time'] ? intval($gets['start_time']) : $endTime-12*3600;

        $postData['header'] = $this->getGbOrderHeader[YII_ENV];
        $postData['body'] = [
            'siteCode'          => 'GB',             // 站点编码
            'createStartTime'   => $startTime,         // 订单创建开始时间
            'createEndTime'     => $endTime,         // 订单创建结束时间
//            'completedStartTime'  => 0,       // 订单支付完成开始时间
//            'completedEndTime'    => 0,       // 订单支付完成结束时间
            'pageNum'             => 1,       // 页码
            'pageSize'             => 0,      // 每页大小，这里设置为0主要是用来获取总数，不需要返回明细数据
            'payStatusList'             => [0,1,2,3,4,5], // 支付状态列表 支付状态：0-未支付，1-部分支付，2-pending，3-已支付,4-退款中，5-已退款
            'orderStatusList'             => [0,1,2,3,4,5],// 订单状态列表 订单状态：0-正常,1-用户取消,2-系统取消,3-运营取消,4-Oms取消,5-取消申请中
            'utmMediumList'             => ["email","mail"]  // edm推广渠道列表
        ];
        $client = new Client(['timeout'=>600]);
        $response = $client->request('POST', $this->gbOrderApiUrl[YII_ENV], ['json' => $postData]);
        $responseJsonString = $response->getBody()->getContents();
        $returnData = \GuzzleHttp\json_decode($responseJsonString,true);
        if ($returnData['body']) {
            $returnBody  = \GuzzleHttp\json_decode($returnData['body'],true);
        }
//            var_dump($returnBody);exit;
        $pageNums = 0;
        if($returnBody['code'] == 0 && isset($returnBody['data']['totalCount'])) {
            $totalOrderNums =  (int)$returnBody['data']['totalCount']; // 当前条件下订单总数
            //分页数量
            $pageNums = ceil($totalOrderNums/$this->pageSize);
        }
        if($pageNums > 0 ){
            $toolkit = new Toolkit();
            $curl = $toolkit->getCurl();
            $curl->onInfo = null;
            //分页获取订单数据
            for ($i = 1;$i <= $pageNums;$i++){
                $curl->add(
                    array(
                        'opt' => array(
                            CURLOPT_URL => Yii::$app->params['web_url'].'/crontab/get-order-info/get-gb-order-goods-data-by-page?site=marketing_email&start_time='.$startTime.'&end_time='.$endTime.'&cur_page='.$i,
                        )
                    )
                );
            }
            $curl->start();
        }

    }

    /**
     * 分页获取GB的订单数据
     * @throws \GuzzleHttp\Exception\GuzzleException
     */
    public function actionGetGbOrderGoodsDataByPage()
    {
        ignore_user_abort(true);
        set_time_limit(0);
        $gets = Yii::$app->request->get();
        // 获取前12小时的数据
        $endTime = isset($gets['end_time']) && $gets['end_time'] ? (int)$gets['end_time'] : \yii::$app->bjTime->strtotime(\yii::$app->bjTime->date("Y-m-d H").":00:00");
        $startTime = isset($gets['start_time']) && $gets['start_time'] ? (int)$gets['start_time'] : $endTime-12*3600;
        //当前的页数
        $curPage = isset($gets['cur_page']) ? $gets['cur_page'] : 1; // 当前页
        //当前的时间
        $curTime = \yii::$app->bjTime->time();

        $client = new Client(['timeout' => 600]);
        $postData = [];
        $postData['header'] = $this->getGbOrderHeader[YII_ENV];
        $postData['body'] = [
            'siteCode'          => 'GB',             // 站点编码
            'createStartTime'   => $startTime,         // 订单创建开始时间
            'createEndTime'     => $endTime,         // 订单创建结束时间
//            'completedStartTime'  => 0,       // 订单支付完成开始时间
//            'completedEndTime'    => 0,       // 订单支付完成结束时间
            'pageNum'             => $curPage,       // 页码
            'pageSize'             => $this->pageSize,      // 每页大小
            'payStatusList'             => [0,1,2,3,4,5], // 支付状态列表 支付状态：0-未支付，1-部分支付，2-pending，3-已支付,4-退款中，5-已退款
            'orderStatusList'             => [0,1,2,3,4,5],// 订单状态列表 订单状态：0-正常,1-用户取消,2-系统取消,3-运营取消,4-Oms取消,5-取消申请中
            'utmMediumList'             => ["email","mail"]  // edm推广渠道列表
        ];
        try{
            $response = $client->request('POST', $this->gbOrderApiUrl[YII_ENV], ['json' => $postData]);
            $responseJsonString = $response->getBody()->getContents();
            $returnData = \GuzzleHttp\json_decode($responseJsonString,true);
            if(!isset($returnData['body']) || empty($returnData['body'])){
                throw new \Exception('从 GB 接口获取数据错误，获取到的信息为：'.$responseJsonString);
            }
            $returnBody  = \GuzzleHttp\json_decode($returnData['body'],true);
            if($returnBody['code'] != 0 || empty($returnBody['data']['list'])){
                throw new \Exception('从 GB 接口获取数据错误，获取到的信息为：'.$responseJsonString);
            }

            $orderInsertArr = $orderMarketInsertArr = $orderGoodsInsertArr = [];
            //插入系统邮件的 SQL
            $insertSql = "insert into gearbest_order_sys_data(site_code,user_id,pipeline_code,order_sn,platform,utm_medium,utm_source,utm_campaign,order_language,created_time,created_date,pay_status,pay_type,pay_channel,completed_date,completed_time,order_amount,shipping_amount,email,country,country_code,province,city,add_time) values";
            //插入营销邮件的 SQL
            $insertMarketSql = "insert into gearbest_order_market_data(site_code,user_id,pipeline_code,order_sn,platform,utm_medium,utm_source,utm_campaign,order_language,created_time,created_date,pay_status,pay_type,pay_channel,completed_date,completed_time,order_amount,shipping_amount,email,country,country_code,province,city,add_time,deal_utm_campaign) values";

            $insertGoodsSql = "insert into gearbest_order_goods_data(goods_sn,goods_name,order_sn,goods_type,goods_label_id,price_type,shop_code,operation_type,qty,goods_amount,add_time) values";

            foreach ($returnBody['data']['list'] as $data) {
                //utmSource 有三种 mail_sys,email_sys,mail_api，其中，前两种为系统邮件，后面一种为营销邮件
                if(in_array($data['innerOrderInfoExtendResp']['utmSource'],['mail_sys','email_sys'])){
                    $orderInsertArr[] = "('{$data['siteCode']}','{$data['userId']}',\"".$data['pipelineCode']."\",'{$data['innerOrderInfoExtendResp']['orderSn']}','{$data['innerOrderInfoExtendResp']['platform']}','{$data['innerOrderInfoExtendResp']['utmMedium']}','{$data['innerOrderInfoExtendResp']['utmSource']}','{$data['innerOrderInfoExtendResp']['utmCampaign']}',\"".$data['orderLanguage']."\",'{$data['createdTime']}','".\yii::$app->bjTime->date("Y-m-d",$data['createdTime'])."','{$data['payStatus']}','{$data['payType']}','{$data['payChannel']}','".\yii::$app->bjTime->date("Y-m-d",$data['completedTime'])."','{$data['completedTime']}','{$data['orderAmount']}','{$data['shippingAmount']}','{$data['innerOrderAddressResp']['email']}',\"".$data['innerOrderAddressResp']['country']."\",'{$data['innerOrderAddressResp']['countryCode']}',\"".addslashes($data['innerOrderAddressResp']['province'])."\",\"".addslashes($data['innerOrderAddressResp']['city'])."\",".$curTime.")";
                }else{
                    //获取处理过后的 utm_campaign
                    $matches = [];
                    preg_match("/^GB_[A-Za-z0-9_]+_\d+_\d+/i", $data['innerOrderInfoExtendResp']['utmCampaign'], $matches);
                    $dealUtmCompaign = !empty($matches) && !empty($matches[0]) ? $matches[0] : '';
                    //订单数据
                    $orderMarketInsertArr[] = "('{$data['siteCode']}','{$data['userId']}',\"".$data['pipelineCode']."\",'{$data['innerOrderInfoExtendResp']['orderSn']}','{$data['innerOrderInfoExtendResp']['platform']}','{$data['innerOrderInfoExtendResp']['utmMedium']}','{$data['innerOrderInfoExtendResp']['utmSource']}','{$data['innerOrderInfoExtendResp']['utmCampaign']}',\"".$data['orderLanguage']."\",'{$data['createdTime']}','".\yii::$app->bjTime->date("Y-m-d",$data['createdTime'])."','{$data['payStatus']}','{$data['payType']}','{$data['payChannel']}','".\yii::$app->bjTime->date("Y-m-d",$data['completedTime'])."','{$data['completedTime']}','{$data['orderAmount']}','{$data['shippingAmount']}','{$data['innerOrderAddressResp']['email']}',\"".$data['innerOrderAddressResp']['country']."\",'{$data['innerOrderAddressResp']['countryCode']}',\"".addslashes($data['innerOrderAddressResp']['province'])."\",\"".addslashes($data['innerOrderAddressResp']['city'])."\",".$curTime.",'".$dealUtmCompaign."')";
                }

                //商品数据
                if(isset($data['innerOrderGoodsRespList']) && is_array($data['innerOrderGoodsRespList'])) {
                    foreach($data['innerOrderGoodsRespList'] as $goodsData) {
                        $goodsData['goodsTitle'] = addslashes($goodsData['goodsTitle']);
                        $orderGoodsInsertArr[] = "('{$goodsData['goodsSn']}','{$goodsData['goodsTitle']}','{$goodsData['orderSn']}','{$goodsData['goodsType']}','{$goodsData['goodsLabelId']}','{$goodsData['priceType']}','{$goodsData['shopCode']}',\"".$goodsData['operationType']."\",'{$goodsData['qty']}','{$goodsData['goodsAmount']}',{$curTime})";

                    }
                }
            }
            //插入系统邮件订单数据
            if($orderInsertArr) {
                $insertSql .= join(',',$orderInsertArr).' ON DUPLICATE KEY UPDATE add_time = '.$curTime.',platform=values(platform),utm_medium=values(utm_medium),utm_source=values(utm_source),utm_campaign=values(utm_campaign),pay_status=values(pay_status),pay_type=values(pay_type),pay_channel=values(pay_channel),completed_date=values(completed_date),completed_time=values(completed_time),order_amount=values(order_amount),shipping_amount=values(shipping_amount),created_time=values(created_time),created_date=values(created_date)';
                // 数据写入edm数据表
                Yii::$app->db->createCommand($insertSql)->execute();
            }
            //插入营销邮件订单数据
            if($orderMarketInsertArr) {
                $insertMarketSql .= join(',',$orderMarketInsertArr).' ON DUPLICATE KEY UPDATE add_time = '.$curTime.',platform=values(platform),utm_medium=values(utm_medium),utm_source=values(utm_source),utm_campaign=values(utm_campaign),pay_status=values(pay_status),pay_type=values(pay_type),pay_channel=values(pay_channel),completed_date=values(completed_date),completed_time=values(completed_time),order_amount=values(order_amount),shipping_amount=values(shipping_amount),deal_utm_campaign=values(deal_utm_campaign),created_time=values(created_time),created_date=values(created_date)';
                // 数据写入edm数据表
                Yii::$app->db->createCommand($insertMarketSql)->execute();
            }
            //插入商品数据
            if($orderGoodsInsertArr) {
                $insertGoodsSql .= join(',',$orderGoodsInsertArr).' ON DUPLICATE KEY UPDATE add_time = '.$curTime.',goods_type=values(goods_type),goods_label_id=values(goods_label_id),price_type=values(price_type),shop_code=values(shop_code),operation_type=values(operation_type),qty=values(qty),goods_amount=values(goods_amount)';
                // 数据写入edm数据表
                Yii::$app->db->createCommand($insertGoodsSql)->execute();
            }
        }catch (\Exception $exp){
            $msg = $exp->getCode().$exp->getLine().$exp->getMessage().$exp->getTraceAsString();
            echo $msg;
            Elog::logCrontab($msg, 'actionNewGetGbOrderGoodsData','info');
        }
    }

    public function actionDealGbOrderData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        
        
        
        while(1) {
            $getSql = "select id,utm_campaign from gearbest_order_market_data where utm_campaign != '' and deal_utm_campaign = '' and add_time = 0 limit 5000";
        
            $orderData = Yii::$app->db->createCommand($getSql)->queryAll();
            if(!$orderData) {
                break;
            }
            $update = $orderIds = [];
            foreach($orderData as $order) {
                $orderIds[] = $order['id'];
                preg_match("/^GB_[A-Za-z0-9_]+_\d+_\d+/i", $order['utm_campaign'], $matches);
                
                if(!empty($matches) && !empty($matches[0])) {
                    $update[] = "WHEN {$order['id']} THEN '{$matches[0]}' ";
                }
                
            }
            
            
            
            if($update) {
                $updateSql = "update gearbest_order_market_data set deal_utm_campaign = CASE id ".join('',$update)." END where id in(".join(',',$orderIds).")";
                
                Yii::$app->db->createCommand($updateSql)->execute();
            }
            if($orderIds) {
                $updateSql = "update gearbest_order_market_data set add_time = ".time()." where id in(".join(',',$orderIds).")";
                
                Yii::$app->db->createCommand($updateSql)->execute();
            }
            
            sleep(2);
        }
        
        echo "数据处理完成！";
    }
    
}